package database;

import java.util.*;

import sharedspace.*;

/**
 * Responsible for data transition from SQL server to the server
 * @author G3
 *
 */
public class CourseControl {
	
	
	private Course course = null;
	private List<Course> list = null;
	
	public CourseControl(){
		
	}
	/**
	 * find all the Courses no exception
	 * @return list of Courses
	 */
	public List<Course> getCourseList(){
		
		int courseSize = JDBC.countStatment("SELECT count(*) FROM course c;");
		
		list = new ArrayList<Course>();
		Object[][] result = new Object[courseSize][Course.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM course c", result, Course.NUM_OF_COLUMNS, courseSize);
		for(int i = 0; i<courseSize; i++)
		{
			course = new Course();
			setCourse(result,i);
			list.add(course);
		}
		return list;
		
	}
	/**
	 * finds course of teacher according to teacherID semester and year 
	 * @param teacherID
	 * @param year
	 * @param semester
	 * @return list <course> 
	 */
	public List<Course> getCourseListByTeacherID(String teacherID,int year, int semester)
	{
		int courseSize = JDBC.countStatment("SELECT count(*) " +
											"FROM teacherincourse tc " +
											"WHERE tc.teacherID="+teacherID + " AND tc.year=" + year + " AND tc.semester=" + semester +";");
		list = new ArrayList<Course>();
		Object[][] result = new Object[courseSize][Course.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * " +
								  "FROM teacherincourse tc " +
								  "WHERE tc.teacherID="+teacherID + " AND tc.year=" + year + " AND tc.semester=" + semester +";"
								  , result,Course.NUM_OF_COLUMNS,courseSize);
		for(int i = 0; i<courseSize; i++)
		{
			course = new Course();
			course = getCourseByCourseID(result[i][1].toString());
			list.add(course);
		}
		return list;
		
	}
	/**
	 * Generates a list of courses that a certain student is studying in a specific year and semester
	 * @param studentID
	 * @param year
	 * @param semester
	 * @return ArrayList<Course> list
	 */
	public List<Course> getCourseListByStudentID(String studentID,int year, int semester)
	{
		int courseSize = JDBC.countStatment("SELECT count(*) " +
											"FROM studentincourse sic " +
											"WHERE sic.studentID='"+studentID + "' AND sic.studentCourseYear=" + year + " AND sic.studentCourseSemester=" + semester +";");
		list = new ArrayList<Course>();
		Object[][] result = new Object[courseSize][Course.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * " +
								  "FROM studentincourse sic " +
								  "WHERE sic.studentID='"+studentID + "' AND sic.studentCourseYear=" + year + " AND sic.studentCourseSemester=" + semester +";"
								  , result,Course.NUM_OF_COLUMNS,courseSize);
		for(int i = 0; i<courseSize; i++)
		{
			course = new Course();
			course = getCourseByCourseID(result[i][1].toString());
			list.add(course);
		}
		return list;
		
	}
	/**
	 * Returns a course of a specific test
	 * @param testID
	 * @return Course course
	 */
	public Course getCourseByTestID(String testID)
	{
		String courseID = testID.substring(2, 4);
		course = getCourseByCourseID(courseID);
		return course;
	}
	/**
	 * Returns an entire Course instance which its ID equals to course ID
	 * @param courseID
	 * @return Course course
	 */
	public Course getCourseByCourseID(String courseID)
	{
		course = new Course();
		Object[] result = new Object[Course.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM course c WHERE c.courseID='" + courseID + "';", result, Course.NUM_OF_COLUMNS);
		setCourse(result);
		return course;
		
	}
	/**
	 * Create new course and set its values according to the data in row in matrix[row][column] result
	 * @param result
	 * @param row
	 */
	public void setCourse(Object[][] result, int row)
	{
		course.setCourseID(result[row][0].toString());
		course.setCourseName(result[row][1].toString());
		course.setCourseCredit(Float.parseFloat(result[row][2].toString()));
		course.setSubjectID(result[row][3].toString());
	}
	/**
	 * Create new subject and set its values according to the data  result[column]
	 * @param result
	 */
	public void setCourse(Object[] result)
	{
		course.setCourseID(result[0].toString());
		course.setCourseName(result[1].toString());
		course.setCourseCredit(Float.parseFloat(result[2].toString()));
		course.setSubjectID(result[3].toString());
	}
	
	/**
	 * Generates a lits of courses which belongs to a specific subject
	 * @param subjectID
	 * @return ArrayList<Course>
	 */
	public List<Course> getCourseListBySubjectID(String subjectID)
	{
		int courseInSubjectSize = JDBC.countStatment("SELECT count(*) FROM course cis where cis.subjectID=" + subjectID + ";");
		list = new ArrayList<Course>();
		Object[][] result = new Object[courseInSubjectSize][Course.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM course cis where cis.subjectID=" + subjectID + ";",result, Course.NUM_OF_COLUMNS, courseInSubjectSize);
		for(int i = 0; i<courseInSubjectSize; i++)
		{
			course = new Course();
			setCourse(result, i);
			list.add(course);
		}//end for
		return list;
	}
		
		public Course getCourseBySubjectID(String subjectID)
		{
			course = new Course();
			Object[] result = new Object[Subject.NUM_OF_COLUMNS];
			JDBC.selectStatment("SELECT * FROM course s WHERE s.subjectID=" + subjectID + ";", result, Subject.NUM_OF_COLUMNS);
			setCourse(result);
			return course;
			
		}
	}// end getCourseListBySubjectID
	
	 /*public static void main(String[] args)
	 {
		 CourseControl c = new CourseControl();
		 Course c1;
		 c1 = c.getCourseByCourseID("02");
		 System.out.println(c1.getCourseID() + " " + c1.getCourseName() + " " + c1.getCourseCredit() + " " + c1.getSubjectID());
		 List<Course> list = new ArrayList<Course>();
		 list = c.getCourseList();
		 for (int i=0;i<list.size();i++)
		 {
			 c1 = list.get(i);
			 System.out.println(c1.getCourseID() + " " + c1.getCourseName() + " " + c1.getCourseCredit() + " " + c1.getSubjectID());
		 }
		 
	 }*/


